/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package limousine.dbservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.DataSource;
import limousine.vo.Remark;

/**
 *
 * @author Tailer
 */
public class RemarkService {

    private DataSource ds = null;

    public DataSource getDs() {
        return ds;
    }

    public void setDs(DataSource ds) {
        this.ds = ds;
    }

    public Remark getByRemarkId(String remarkId) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * From remark where REMARK_ID = '" + remarkId.trim() + "' ");
        Remark o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Remark();
                o.setRemarkId(rs.getString("REMARK_ID"));
                o.setRemark(rs.getString("REMARK"));
                o.setRemarkOrder(rs.getInt("REMARK_ORDER"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public boolean insert(Remark o) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO remark(REMARK_ID,REMARK,REMARK_ORDER ) ");
        sql.append("VALUES (?,?,?)");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getRemarkId());
            stmt.setString(2, o.getRemark());
            stmt.setInt(3, o.getRemarkOrder());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean update(Remark o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE remark ");
        sql.append("SET REMARK=?,REMARK_ORDER=? ");
        sql.append("where REMARK_ID='" + o.getRemarkId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getRemark());
            stmt.setInt(2, o.getRemarkOrder());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteByRemarkId(String carId) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from remark where REMARK_ID = '" + carId.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}
